<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>10.2. Zend_Db_Statement</title>
<link rel="stylesheet" href="dbstyle.css" type="text/css">
<meta name="generator" content="DocBook XSL Stylesheets V1.72.0">
<link rel="start" href="index.html" title="Programmer's Reference Guide">
<link rel="up" href="zend.db.html" title="Chapter 10. Zend_Db">
<link rel="prev" href="zend.db.html" title="Chapter 10. Zend_Db">
<link rel="next" href="zend.db.profiler.html" title="10.3. Zend_Db_Profiler">
<link rel="chapter" href="introduction.html" title="Chapter 1. Introduction to Zend Framework">
<link rel="chapter" href="zend.acl.html" title="Chapter 2. Zend_Acl">
<link rel="chapter" href="zend.auth.html" title="Chapter 3. Zend_Auth">
<link rel="chapter" href="zend.cache.html" title="Chapter 4. Zend_Cache">
<link rel="chapter" href="zend.config.html" title="Chapter 5. Zend_Config">
<link rel="chapter" href="zend.console.getopt.html" title="Chapter 6. Zend_Console_Getopt">
<link rel="chapter" href="zend.controller.html" title="Chapter 7. Zend_Controller">
<link rel="chapter" href="zend.currency.html" title="Chapter 8. Zend_Currency">
<link rel="chapter" href="zend.date.html" title="Chapter 9. Zend_Date">
<link rel="chapter" href="zend.db.html" title="Chapter 10. Zend_Db">
<link rel="chapter" href="zend.debug.html" title="Chapter 11. Zend_Debug">
<link rel="chapter" href="zend.dojo.html" title="Chapter 12. Zend_Dojo">
<link rel="chapter" href="zend.dom.html" title="Chapter 13. Zend_Dom">
<link rel="chapter" href="zend.exception.html" title="Chapter 14. Zend_Exception">
<link rel="chapter" href="zend.feed.html" title="Chapter 15. Zend_Feed">
<link rel="chapter" href="zend.filter.html" title="Chapter 16. Zend_Filter">
<link rel="chapter" href="zend.form.html" title="Chapter 17. Zend_Form">
<link rel="chapter" href="zend.gdata.html" title="Chapter 18. Zend_Gdata">
<link rel="chapter" href="zend.http.html" title="Chapter 19. Zend_Http">
<link rel="chapter" href="zend.infocard.html" title="Chapter 20. Zend_InfoCard">
<link rel="chapter" href="zend.json.html" title="Chapter 21. Zend_Json">
<link rel="chapter" href="zend.layout.html" title="Chapter 22. Zend_Layout">
<link rel="chapter" href="zend.ldap.html" title="Chapter 23. Zend_Ldap">
<link rel="chapter" href="zend.loader.html" title="Chapter 24. Zend_Loader">
<link rel="chapter" href="zend.locale.html" title="Chapter 25. Zend_Locale">
<link rel="chapter" href="zend.log.html" title="Chapter 26. Zend_Log">
<link rel="chapter" href="zend.mail.html" title="Chapter 27. Zend_Mail">
<link rel="chapter" href="zend.measure.html" title="Chapter 28. Zend_Measure">
<link rel="chapter" href="zend.memory.html" title="Chapter 29. Zend_Memory">
<link rel="chapter" href="zend.mime.html" title="Chapter 30. Zend_Mime">
<link rel="chapter" href="zend.openid.html" title="Chapter 31. Zend_OpenId">
<link rel="chapter" href="zend.paginator.html" title="Chapter 32. Zend_Paginator">
<link rel="chapter" href="zend.pdf.html" title="Chapter 33. Zend_Pdf">
<link rel="chapter" href="zend.registry.html" title="Chapter 34. Zend_Registry">
<link rel="chapter" href="zend.rest.html" title="Chapter 35. Zend_Rest">
<link rel="chapter" href="zend.search.lucene.html" title="Chapter 36. Zend_Search_Lucene">
<link rel="chapter" href="zend.server.html" title="Chapter 37. Zend_Server">
<link rel="chapter" href="zend.service.html" title="Chapter 38. Zend_Service">
<link rel="chapter" href="zend.session.html" title="Chapter 39. Zend_Session">
<link rel="chapter" href="zend.soap.html" title="Chapter 40. Zend_Soap">
<link rel="chapter" href="zend.test.html" title="Chapter 41. Zend_Test">
<link rel="chapter" href="zend.text.html" title="Chapter 42. Zend_Text">
<link rel="chapter" href="zend.timesync.html" title="Chapter 43. Zend_TimeSync">
<link rel="chapter" href="zend.translate.html" title="Chapter 44. Zend_Translate">
<link rel="chapter" href="zend.uri.html" title="Chapter 45. Zend_Uri">
<link rel="chapter" href="zend.validate.html" title="Chapter 46. Zend_Validate">
<link rel="chapter" href="zend.version.html" title="Chapter 47. Zend_Version">
<link rel="chapter" href="zend.view.html" title="Chapter 48. Zend_View">
<link rel="chapter" href="zend.xmlrpc.html" title="Chapter 49. Zend_XmlRpc">
<link rel="appendix" href="requirements.html" title="Appendix A. Zend Framework Requirements">
<link rel="appendix" href="coding-standard.html" title="Appendix B. Zend Framework Coding Standard for PHP">
<link rel="appendix" href="copyrights.html" title="Appendix C. Copyright Information">
<link rel="index" href="the.index.html" title="Index">
<link rel="subsection" href="zend.db.statement.html#zend.db.statement.creating" title="10.2.1. Creating a Statement">
<link rel="subsection" href="zend.db.statement.html#zend.db.statement.executing" title="10.2.2. Executing a Statement">
<link rel="subsection" href="zend.db.statement.html#zend.db.statement.fetching" title="10.2.3. Fetching Results from a SELECT Statement">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF">
<div class="navheader"><table width="100%" summary="Navigation header">
<tr><th colspan="3" align="center">10.2. Zend_Db_Statement</th></tr>
<tr>
<td width="20%" align="left">
<a accesskey="p" href="zend.db.html">Prev</a> </td>
<th width="60%" align="center">Chapter 10. Zend_Db</th>
<td width="20%" align="right"> <a accesskey="n" href="zend.db.profiler.html">Next</a>
</td>
</tr>
</table></div>
<div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="zend.db.statement"></a>10.2. Zend_Db_Statement</h2></div></div></div>
<p>
        In addition to convenient methods such as <code class="code">fetchAll()</code> and
        <code class="code">insert()</code> documented in <a href="zend.db.html#zend.db.adapter" title="10.1. Zend_Db_Adapter">Section 10.1, “Zend_Db_Adapter”</a>,
        you can use a statement object to gain more options for running
        queries and fetching result sets.  This section describes how to get an
        instance of a statement object, and how to use its methods.
    </p>
<p>
        Zend_Db_Statement is based on the PDOStatement object in the
        <a href="http://www.php.net/pdo" target="_top">PHP Data Objects</a> extension.
    </p>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="zend.db.statement.creating"></a>10.2.1. Creating a Statement</h3></div></div></div>
<p>
            Typically, a statement object is returned by the
            <code class="code">query()</code> method of the database Adapter class.
            This method is a general way to prepare any SQL statement.
            The first argument is a string containing an SQL statement.
            The optional second argument is an array of values to bind
            to parameter placeholders in the SQL string.
        </p>
<div class="example">
<a name="zend.db.statement.creating.example1"></a><p class="title"><b>Example 10.32. Creating a SQL statement object with query()</b></p>
<div class="example-contents"><pre class="programlisting">&lt;?php
$stmt = $db-&gt;query('SELECT * FROM bugs WHERE reported_by = ? AND bug_status = ?',
                array('goofy', 'FIXED'));
            </pre></div>
</div>
<br class="example-break"><p>
            The statement object corresponds to a SQL statement that has been
            prepared, and executed once with the bind-values specified.
            If the statement was a SELECT query or other type of statement
            that returns a result set, it is now ready to fetch results.
        </p>
<p>
            You can create a statement with its constructor, but this is less
            typical usage.  There is no factory method to create this object,
            so you need to load the specific statement class and call its
            constructor.  Pass the Adapter object as the first argument, and
            a string containing an SQL statement as the second argument.
            The statement is prepared, but not executed.
        </p>
<div class="example">
<a name="zend.db.statement.creating.example2"></a><p class="title"><b>Example 10.33. Using a SQL statement constructor</b></p>
<div class="example-contents"><pre class="programlisting">&lt;?php
require_once 'Zend/Db/Statement/Mysqli.php';

$sql = 'SELECT * FROM bugs WHERE reported_by = ? AND bug_status = ?';

$stmt = new Zend_Db_Statement_Mysqli($db, $sql);
            </pre></div>
</div>
<br class="example-break">
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="zend.db.statement.executing"></a>10.2.2. Executing a Statement</h3></div></div></div>
<p>
            You need to execute a statement object if you create it using its
            constructor, or if you want to execute the same statement multiple
            times.  Use the <code class="code">execute()</code> method of the statement
            object.  The single argument is an array of value to bind to
            parameter placeholders in the statement.
        </p>
<p>
            If you use <span class="emphasis"><em>positional parameters</em></span>, or those
            that are marked with a question mark symbol (<code class="code">?</code>), pass
            the bind values in a plain array.
        </p>
<div class="example">
<a name="zend.db.statement.executing.example1"></a><p class="title"><b>Example 10.34. Executing a statement with positional parameters</b></p>
<div class="example-contents"><pre class="programlisting">&lt;?php
$sql = 'SELECT * FROM bugs WHERE reported_by = ? AND bug_status = ?';

$stmt = new Zend_Db_Statement_Mysqli($db, $sql);

$stmt-&gt;execute(array('goofy', 'FIXED'));
            </pre></div>
</div>
<br class="example-break"><p>
            If you use <span class="emphasis"><em>named parameters</em></span>, or those that are
            indicated by a string identifier preceded by a colon character
            (<code class="code">:</code>), pass the bind values in an associative array.
            The keys of this array should match the parameter names.
        </p>
<div class="example">
<a name="zend.db.statement.executing.example2"></a><p class="title"><b>Example 10.35. Executing a statement with named parameters</b></p>
<div class="example-contents"><pre class="programlisting">&lt;?php
$sql = 'SELECT * FROM bugs WHERE reported_by = :reporter AND bug_status = :status';

$stmt = new Zend_Db_Statement_Mysqli($db, $sql);

$stmt-&gt;execute(array(':reporter' =&gt; 'goofy', ':status' =&gt; 'FIXED'));
            </pre></div>
</div>
<br class="example-break"><p>
            PDO statements support both positional parameters and named
            parameters, but not both types in a single SQL statement.  Some of
            the Zend_Db_Statement classes for non-PDO extensions may support
            only one type of parameter or the other.
        </p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="zend.db.statement.fetching"></a>10.2.3. Fetching Results from a <code class="code">SELECT</code> Statement</h3></div></div></div>
<p>
            You can call methods on the statement object to retrieve rows from
            SQL statements that produce result set.  SELECT, SHOW, DESCRIBE and
            EXPLAIN are examples of statements that produce a result set.
            INSERT, UPDATE, and DELETE are examples of statements that don't
            produce a result set.  You can execute the latter SQL statements
            using Zend_Db_Statement, but you cannot call methods to fetch rows
            of results from them.
        </p>
<div class="sect3" lang="en">
<div class="titlepage"><div><div><h4 class="title">
<a name="zend.db.statement.fetching.fetch"></a>10.2.3.1. Fetching a Single Row from a Result Set</h4></div></div></div>
<p>
                To retrieve one row from the result set, use the
                <code class="code">fetch()</code> method of the statement object.
                All three arguments of this method are optional:
            </p>
<div class="itemizedlist"><ul type="disc">
<li><p>
                        <span class="strong"><strong>Fetch style</strong></span> is the
                        first argument.  This controls the structure in which
                        the row is returned.
                        See <a href="zend.db.html#zend.db.adapter.select.fetch-mode" title="10.1.3.2. Changing the Fetch Mode">Section 10.1.3.2, “Changing the Fetch Mode”</a>
                        for a description of the valid values and the
                        corresponding data formats.
                    </p></li>
<li><p>
                        <span class="strong"><strong>Cursor orientation</strong></span>
                        is the second argument.  The default is
                        Zend_Db::FETCH_ORI_NEXT, which simply means that each
                        call to <code class="code">fetch()</code> returns the next row in
                        the result set, in the order returned by the RDBMS.
                    </p></li>
<li><p>
                        <span class="strong"><strong>Offset</strong></span> is the third
                        argument.
                        If the cursor orientation is Zend_Db::FETCH_ORI_ABS,
                        then the offset number is the ordinal number of the row
                        to return.
                        If the cursor orientation is Zend_Db::FETCH_ORI_REL,
                        then the offset number is relative to the cursor
                        position before <code class="code">fetch()</code> was called.
                    </p></li>
</ul></div>
<p>
                <code class="code">fetch()</code> returns <code class="code">false</code> if all rows of
                the result set have been fetched.
            </p>
<div class="example">
<a name="zend.db.statement.fetching.fetch.example"></a><p class="title"><b>Example 10.36. Using fetch() in a loop</b></p>
<div class="example-contents"><pre class="programlisting">&lt;?php
$stmt = $db-&gt;query('SELECT * FROM bugs');

while ($row = $stmt-&gt;fetch()) {
    echo $row['bug_description'];
}
                </pre></div>
</div>
<br class="example-break"><p>
                See also <a href="http://www.php.net/PDOStatement-fetch" target="_top">PDOStatement::fetch()</a>.
            </p>
</div>
<div class="sect3" lang="en">
<div class="titlepage"><div><div><h4 class="title">
<a name="zend.db.statement.fetching.fetchall"></a>10.2.3.2. Fetching a Complete Result Set</h4></div></div></div>
<p>
                To retrieve all the rows of the result set in one step, use the
                <code class="code">fetchAll()</code> method.  This is equivalent to calling
                the <code class="code">fetch()</code> method in a loop and returning all
                the rows in an array.  The <code class="code">fetchAll()</code> method accepts
                two arguments.  The first is the fetch style, as described above,
                and the second indicates the number of the column to return,
                when the fetch style is Zend_Db::FETCH_COLUMN.
            </p>
<div class="example">
<a name="zend.db.statement.fetching.fetchall.example"></a><p class="title"><b>Example 10.37. Using fetchAll()</b></p>
<div class="example-contents"><pre class="programlisting">&lt;?php
$stmt = $db-&gt;query('SELECT * FROM bugs');

$rows = $stmt-&gt;fetchAll();

echo $rows[0]['bug_description'];
                </pre></div>
</div>
<br class="example-break"><p>
                See also <a href="http://www.php.net/PDOStatement-fetchAll" target="_top">PDOStatement::fetchAll()</a>.
            </p>
</div>
<div class="sect3" lang="en">
<div class="titlepage"><div><div><h4 class="title">
<a name="zend.db.statement.fetching.fetch-mode"></a>10.2.3.3. Changing the Fetch Mode</h4></div></div></div>
<p>
                By default, the statement object returns rows of the result set
                as associative arrays, mapping column names to column values.
                You can specify a different format for the statement class to
                return rows, just as you can in the Adapter class.  You can use
                the <code class="code">setFetchMode()</code> method of the statement object
                to specify the fetch mode.  Specify the fetch mode using
                Zend_Db class constants FETCH_ASSOC, FETCH_NUM, FETCH_BOTH,
                FETCH_COLUMN, and FETCH_OBJ.
                See <a href="zend.db.html#zend.db.adapter.select.fetch-mode" title="10.1.3.2. Changing the Fetch Mode">Section 10.1.3.2, “Changing the Fetch Mode”</a>
                for more information on these modes.
                Subsequent calls to the statement methods <code class="code">fetch()</code>
                or <code class="code">fetchAll()</code> use the fetch mode that you specify.
            </p>
<div class="example">
<a name="zend.db.statement.fetching.fetch-mode.example"></a><p class="title"><b>Example 10.38. Setting the fetch mode</b></p>
<div class="example-contents"><pre class="programlisting">&lt;?php
$stmt = $db-&gt;query('SELECT * FROM bugs');

$stmt-&gt;setFetchMode(Zend_Db::FETCH_NUM);

$rows = $stmt-&gt;fetchAll();

echo $rows[0][0];
                </pre></div>
</div>
<br class="example-break"><p>
                See also <a href="http://www.php.net/PDOStatement-setFetchMode" target="_top">PDOStatement::setFetchMode()</a>.
            </p>
</div>
<div class="sect3" lang="en">
<div class="titlepage"><div><div><h4 class="title">
<a name="zend.db.statement.fetching.fetchcolumn"></a>10.2.3.4. Fetching a Single Column from a Result Set</h4></div></div></div>
<p>
                To return a single column from the next row of the result set,
                use <code class="code">fetchColumn()</code>.  The optional argument is the
                integer index of the column, and it defaults to 0.  This method
                returns a scalar value, or <code class="code">false</code> if all rows of
                the result set have been fetched.
            </p>
<p>
                Note this method operates differently than the
                <code class="code">fetchCol()</code> method of the Adapter class.
                The <code class="code">fetchColumn()</code> method of a statement returns a
                single value from one row.
                The <code class="code">fetchCol()</code> method of an adapter returns an
                array of values, taken from the first column of all rows of the
                result set.
            </p>
<div class="example">
<a name="zend.db.statement.fetching.fetchcolumn.example"></a><p class="title"><b>Example 10.39. Using fetchColumn()</b></p>
<div class="example-contents"><pre class="programlisting">&lt;?php
$stmt = $db-&gt;query('SELECT bug_id, bug_description, bug_status FROM bugs');

$bug_status = $stmt-&gt;fetchColumn(2);
                </pre></div>
</div>
<br class="example-break"><p>
                See also <a href="http://www.php.net/PDOStatement-fetchColumn" target="_top">PDOStatement::fetchColumn()</a>.
            </p>
</div>
<div class="sect3" lang="en">
<div class="titlepage"><div><div><h4 class="title">
<a name="zend.db.statement.fetching.fetchobject"></a>10.2.3.5. Fetching a Row as an Object</h4></div></div></div>
<p>
                To retrieve a row from the result set structured as an object,
                use the <code class="code">fetchObject()</code>.  This method takes two
                optional arguments.  The first argument is a string that names
                the class name of the object to return; the default is
                'stdClass'.  The second argument is an array of values that
                will be passed to the constructor of that class.
            </p>
<div class="example">
<a name="zend.db.statement.fetching.fetchobject.example"></a><p class="title"><b>Example 10.40. Using fetchObject()</b></p>
<div class="example-contents"><pre class="programlisting">&lt;?php
$stmt = $db-&gt;query('SELECT bug_id, bug_description, bug_status FROM bugs');

$obj = $stmt-&gt;fetchObject();

echo $obj-&gt;bug_description;
                </pre></div>
</div>
<br class="example-break"><p>
                See also <a href="http://www.php.net/PDOStatement-fetchObject" target="_top">PDOStatement::fetchObject()</a>.
            </p>
</div>
</div>
</div>
<div class="navfooter"><table width="100%" summary="Navigation footer">
<tr>
<td width="40%" align="left">
<a accesskey="p" href="zend.db.html">Prev</a> </td>
<td width="20%" align="center"><a accesskey="u" href="zend.db.html">Up</a></td>
<td width="40%" align="right"> <a accesskey="n" href="zend.db.profiler.html">Next</a>
</td>
</tr>
<tr>
<td width="40%" align="left" valign="top">Chapter 10. Zend_Db </td>
<td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td>
<td width="40%" align="right" valign="top"> 10.3. Zend_Db_Profiler</td>
</tr>
</table></div>
<div class="revinfo"></div>
</body>
</html>
